-- @owner: cr13
-- @date: 2025/3/27
-- @testpoint:视图依赖自定义函数，重建的函数返回值类型和原有函数不一样，部分合理报错
--step1: 创建 products 表
drop table if exists products;
CREATE TABLE products (
product_id SERIAL PRIMARY KEY,
product_name VARCHAR(100),
price DECIMAL(10, 2)
);

--step2: 插入示例数据
INSERT INTO products (product_name, price) VALUES
('Product A', 100.00),
('Product B', 200.00),
('Product C', 300.00);
--step3: 创建自定义函数
drop function if exists func_ddl0029;
CREATE FUNCTION calculate_discounted_price(
original_price DECIMAL(10, 2),
discount_rate DECIMAL(3, 2)
)
RETURNS DECIMAL(10, 2) AS $$
BEGIN
RETURN original_price * (1 - discount_rate);
END;
$$ LANGUAGE plpgsql;
/
--step4: 创建视图，引用自定义函数
drop view if exists product_discount_view;
CREATE VIEW product_discount_view AS
SELECT
product_id,
product_name,
price AS original_price,
calculate_discounted_price(price, 0.1) AS discounted_price
FROM
products;
--step5: 查询视图
select * from product_discount_view;
--step6: 删除函数，成功
drop FUNCTION calculate_discounted_price;
--step7: 查询视图，报错
select * from product_discount_view;
--step8: 查询视图状态，无效
select valid from pg_object where object_oid='product_discount_view'::regclass;
--step9: 重建函数，返回值类型不一样，成功
CREATE FUNCTION calculate_discounted_price(
original_price DECIMAL(10, 2),
discount_rate DECIMAL(3, 2)
)
RETURNS numeric(10, 2) AS $$
BEGIN
RETURN original_price * (1 - discount_rate);
END;
$$ LANGUAGE plpgsql;
/
--step10: 查询视图状态，无效
select valid from pg_object where object_oid='product_discount_view'::regclass;
--step11: 查询视图，成功
select * from product_discount_view;
--step12: 查询视图状态，有效
select valid from pg_object where object_oid='product_discount_view'::regclass;
--step13: 查询视图定义，成功
select pg_get_viewdef('product_discount_view');
--step14: 删除函数
drop FUNCTION calculate_discounted_price;
--step15: 重建函数，返回类型不一样且无法和原始类型转换，成功
CREATE FUNCTION calculate_discounted_price(
original_price DECIMAL(10, 2),
discount_rate DECIMAL(3, 2)
)
RETURNS time AS $$
BEGIN
RETURN original_price * (1 - discount_rate);
END;
$$ LANGUAGE plpgsql;
/
--step16: 查询视图，报错
select * from product_discount_view;
--step17: 查询视图状态，无效
select valid from pg_object where object_oid='product_discount_view'::regclass;
--step18: 查询视图定义，成功
select pg_get_viewdef('product_discount_view');
--step19: 清理环境
drop table products;
drop function calculate_discounted_price;
drop view product_discount_view;
